package com.study.manage.repository;


import com.study.manage.entity.PermissionVO;
import com.study.manage.entity.UserRolesVO;
import com.study.manage.pojo.Permission;
import com.study.manage.pojo.Role;
import com.study.manage.pojo.User;
import com.study.manage.pojo.UserRoleKey;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Component;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.transaction.Transactional;
import java.util.Date;
import java.util.List;


@Component
public interface UserRepository extends JpaRepository<User,Integer>, JpaSpecificationExecutor<User> {

    @Query("select  new com.study.manage.entity.UserRolesVO(r.id, r.username, r.mobile)" +
            " from User as r " +
            " where r.id =:id")
    UserRolesVO getUserRolesVOById1(Integer id);

    //简单的扩展-以字段为关键字进行查询 findByXXX(xxx) 其中 XXX 对应数据库中的字段
    List<User> findByMobile(String mobile);

    List<User> findByUsername(String username);

    List<User> findByMobileAndId(String mobile, String id);

    List<User> findByMobileOrId(String mobile, String id);

    @Modifying
    @Transactional
    @Query("update User a set " +
            "a.username = CASE WHEN :#{#user.username} IS NULL THEN a.username ELSE :#{#user.username} END ," +
            "a.mobile = CASE WHEN :#{#user.mobile} IS NULL THEN a.mobile ELSE :#{#user.mobile} END ," +
            "a.email = CASE WHEN :#{#user.email} IS NULL THEN a.email ELSE :#{#user.email} END ," +
            "a.password = CASE WHEN :#{#user.password} IS NULL THEN a.password ELSE :#{#user.password} END ," +
            "a.insertUid = CASE WHEN :#{#user.insertUid} IS NULL THEN a.insertUid ELSE :#{#user.insertUid} END ," +
            "a.insertTime = CASE WHEN :#{#user.insertTime} IS NULL THEN a.insertTime ELSE :#{#user.insertTime} END ," +
            "a.updateTime = CASE WHEN :#{#user.updateTime} IS NULL THEN a.updateTime ELSE :#{#user.updateTime} END ," +
            "a.isDel = CASE WHEN :#{#user.isDel} IS NULL THEN a.isDel ELSE :#{#user.isDel} END ," +
            "a.isJob = CASE WHEN :#{#user.isJob} IS NULL THEN a.isJob ELSE :#{#user.isJob} END ," +
            "a.mcode =  CASE WHEN :#{#user.mcode} IS NULL THEN a.mcode ELSE :#{#user.mcode} END ," +
            "a.version =  CASE WHEN :#{#user.version} IS NULL THEN 1 ELSE :#{#user.version + 1} END ," +
            "a.sendTime =  CASE WHEN :#{#user.sendTime} IS NULL THEN a.sendTime ELSE :#{#user.sendTime} END " +
            "where a.id = :#{#user.id}")
    int update(@Param("user") User user);


    //    //查询2个表的指定字段，组成新的对象Role
    @Query(value = "select a.user_id, GROUP_CONCAT(b.role_name) names from user_role a "
            + "left join (select id, role_name, code from role) b on a.role_id = b.id group by a.user_id", nativeQuery = true)
    List<String[]> getUserIdandRole();

    @Query(value = "select a from User a "
            + "where a.username = :username  and a.password = :password and a.isDel = 0 and a.isJob = 0" )
    User findUserByUsernameAndPassword(@Param("username") String username,
                                       @Param("password") String password);

    //查询2个表的指定字段，组成新的String
    @Query(value = "select GROUP_CONCAT(b.role_name) names from user_role a "
            + "left join (select id, role_name, code from role) b on a.role_id = b.id where"
            + " a.user_id = :userid", nativeQuery = true)
    String getRoleNamesByUserId(@Param("userid") Integer userid);

    //查询2个表的指定字段，组成新的对象Role
//    @Query(value = "select a from Role a "
//            + "left join UserRoleKey b on a.id = b.roleId where b.userId = :userid")
//    List<Role> getRolesByUserId(@Param("userid") Integer userid);

    /**
     * 根据用户id获取角色数据
     * @param userId
     * @return
     */
    @Query("select r.roles" +
            " from User as r " +
            " where r.id =:userId")
    List<Role> getRoleByUserId(Integer userId);

    /**
     * 修改用户密码
     * @param id
     * @param password
     * @return
     */
    @Modifying
    @Transactional
    @Query("update User a set " +
            "a.password = :password ," +
            "a.updateTime = :date " +
            "where a.id = :id and a.isJob = false and a.isDel = false ")
    int updatePwd(@Param("id") Integer id, @Param("password") String password, @Param("date") Date date);

    @Modifying
    @Transactional
    @Query(value = "update user a set " +
            "a.is_lock = :isLock ," +
            "a.update_time = :date " +
            "where a.id = :id and a.is_job = 0 and a.is_del = false ", nativeQuery = true)
    int setUserLockNum(@Param("id") Integer id, @Param("isLock") Integer isLock, @Param("date") Date date);

    /**
     * 批量删除用戶角色中间表数据
     * @param userId
     */
    @Transactional
    @Modifying
    @Query(value = "delete from user_role where user_id = :userId", nativeQuery = true)
    void batchDelUserRoles(int userId);

    @Transactional
    @Modifying
    @Query(value = "INSERT INTO user_role(user_id, role_id) VALUES(?1, ?2)", nativeQuery = true)
    void saveUserRole(int user_id, int role_id);
}
